﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CarLoanSystem.Entity;

namespace CarLoanSystem.DAL
{
    public class LoanInfoDAL : DBBase
    {
        public static LoanItem LoadLoan(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [LOAN_ID],[CAR_ID],[INTERREST_ID],[DOWN_PAYMENT_ID]
                                                      ,[CUSTOMER_ID],[VEHICLE_ID],[LOAN_DATE],[LOAN_SUCCESS_DATE]
                                                      ,[DOWN_PAYMENT],[PAY_MONTH],[DEBT_MONTH_REST],[PRINCIPLE]
                                                      ,[LOAN_STATUS],[DEBT_STATUS],[CHECK_IDENTITY_CARD]
                                                      ,[CHECK_REGISTRATION],[CHECK_SALARY_SLIP],[CHECK_ACCOUNT]
                                                  FROM [CAR_LOAN].[dbo].[LOAN] WHERE [LOAN_ID] = @LOAN_ID"
                                                       , DBBase.con);
                command.Parameters.AddWithValue("@LOAN_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    LoanItem item = new LoanItem();
                    item.LOAN_ID = Convert.ToInt32(dt.Rows[0]["LOAN_ID"].ToString());
                    item.CAR_ID = Convert.ToInt32(dt.Rows[0]["CAR_ID"].ToString());
                    item.INTERREST_ID = Convert.ToInt32(dt.Rows[0]["INTERREST_ID"].ToString());
                    item.DOWN_PAYMENT_ID = Convert.ToInt32(dt.Rows[0]["DOWN_PAYMENT_ID"].ToString());
                    item.CUSTOMER_ID = Convert.ToInt32(dt.Rows[0]["CUSTOMER_ID"].ToString());
                    item.VEHICLE_ID = dt.Rows[0]["VEHICLE_ID"].ToString();
                    item.LOAN_DATE = Convert.ToDateTime(dt.Rows[0]["LOAN_DATE"].ToString());
                    if (!dt.Rows[0]["LOAN_SUCCESS_DATE"].ToString().Equals(""))
                    {
                        item.LOAN_SUCCESS_DATE = Convert.ToDateTime(dt.Rows[0]["LOAN_SUCCESS_DATE"].ToString());
                    }
                    item.DOWN_PAYMENT = Convert.ToDecimal(dt.Rows[0]["DOWN_PAYMENT"].ToString());
                    item.PAY_MONTH = Convert.ToDecimal(dt.Rows[0]["PAY_MONTH"].ToString());
                    item.DEBT_MONTH_REST = Convert.ToDecimal(dt.Rows[0]["DEBT_MONTH_REST"].ToString());
                    item.PRINCIPLE = Convert.ToDecimal(dt.Rows[0]["PRINCIPLE"].ToString());
                    item.LOAN_STATUS = dt.Rows[0]["LOAN_STATUS"].ToString();
                    item.DEBT_STATUS = dt.Rows[0]["DEBT_STATUS"].ToString();
                    item.CHECK_IDENTITY_CARD = dt.Rows[0]["CHECK_IDENTITY_CARD"].ToString();
                    item.CHECK_REGISTRATION = dt.Rows[0]["CHECK_REGISTRATION"].ToString();
                    item.CHECK_SALARY_SLIP = dt.Rows[0]["CHECK_SALARY_SLIP"].ToString();
                    item.CHECK_ACCOUNT = dt.Rows[0]["CHECK_ACCOUNT"].ToString();
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllLoanHead(string loanId = "", string name = "", string lastName = "", int empId = 0)
        {
            try
            {
                string sqlQuery = @"SELECT dbo.LOAN.LOAN_ID, dbo.CUSTOMER.CUSTOMER_NAME, dbo.CUSTOMER.CUSTOMER_LASTNAME, dbo.LOAN.INTERREST_ID, 
                      dbo.LOAN.DOWN_PAYMENT_ID, dbo.LOAN.LOAN_DATE, dbo.LOAN.DOWN_PAYMENT, dbo.LOAN.PAY_MONTH, dbo.LOAN.DEBT_MONTH_REST, 
                      dbo.LOAN.LOAN_STATUS, dbo.DOWN_PAYMENT_INFO.DOWN_PAYMENT_RATE, dbo.INTERREST_INFO.INTERREST_RATE, dbo.INTERREST_INFO.DEBT_MONTH, 
                      dbo.LOAN.PRINCIPLE
                    FROM dbo.CUSTOMER INNER JOIN
                      dbo.LOAN ON dbo.CUSTOMER.CUSTOMER_ID = dbo.LOAN.CUSTOMER_ID INNER JOIN
                      dbo.DOWN_PAYMENT_INFO ON dbo.LOAN.DOWN_PAYMENT_ID = dbo.DOWN_PAYMENT_INFO.DOWN_PAYMENT_ID INNER JOIN
                      dbo.INTERREST_INFO ON dbo.LOAN.INTERREST_ID = dbo.INTERREST_INFO.INTERREST_ID INNER JOIN
                      dbo.SCHEDULE ON dbo.LOAN.LOAN_ID = dbo.SCHEDULE.LOAN_ID INNER JOIN
                      dbo.EMPLOYEE ON dbo.SCHEDULE.EMPLOYEE_ID = dbo.EMPLOYEE.EMPLOYEE_ID INNER JOIN
                      dbo.EMPLOYEE AS EMPLOYEE_1 ON dbo.EMPLOYEE.EMPLOYEE_GROUP = EMPLOYEE_1.EMPLOYEE_GROUP
                    WHERE (dbo.LOAN.LOAN_STATUS <> 'I' AND dbo.LOAN.LOAN_STATUS <> 'F') AND LOAN.LOAN_STATUS = 'C' and (EMPLOYEE_1.EMPLOYEE_ID = '" + empId + "')";

                if (loanId != "")
                {
                    sqlQuery += "and [LOAN_ID] = " + loanId;
                }
                if (name != "")
                {
                    sqlQuery += "and [CUSTOMER_NAME] like '%" + name + "%'";
                }
                if (lastName != "")
                {
                    sqlQuery += "and [CUSTOMER_LASTNAME] like '%" + lastName + "%'";
                }

                DBBase.ConncetDatabase();

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();

                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                    DBBase.DisConncetDatabase();
                    return dt;
                }
                else
                {
                    DBBase.DisConncetDatabase();
                    return null;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllLoanAdmin(string loanId = "", string name = "", string lastName = "")
        {
            try
            {
                string sqlQuery = @"SELECT dbo.LOAN.LOAN_ID, dbo.CUSTOMER.CUSTOMER_NAME, dbo.CUSTOMER.CUSTOMER_LASTNAME,
                     dbo.LOAN.INTERREST_ID,dbo.LOAN.DOWN_PAYMENT_ID, dbo.LOAN.LOAN_DATE, dbo.LOAN.DOWN_PAYMENT, dbo.LOAN.PAY_MONTH, dbo.LOAN.DEBT_MONTH_REST, 
                     dbo.LOAN.LOAN_STATUS, dbo.DOWN_PAYMENT_INFO.DOWN_PAYMENT_RATE, dbo.INTERREST_INFO.INTERREST_RATE, 
                     dbo.INTERREST_INFO.DEBT_MONTH, dbo.LOAN.PRINCIPLE
                        FROM dbo.CUSTOMER INNER JOIN
                     dbo.LOAN ON dbo.CUSTOMER.CUSTOMER_ID = dbo.LOAN.CUSTOMER_ID INNER JOIN
                     dbo.DOWN_PAYMENT_INFO ON dbo.LOAN.DOWN_PAYMENT_ID = dbo.DOWN_PAYMENT_INFO.DOWN_PAYMENT_ID INNER JOIN
                     dbo.INTERREST_INFO ON dbo.LOAN.INTERREST_ID = dbo.INTERREST_INFO.INTERREST_ID INNER JOIN
                     dbo.SCHEDULE ON dbo.LOAN.LOAN_ID = dbo.SCHEDULE.LOAN_ID WHERE (dbo.LOAN.LOAN_STATUS = 'A' AND dbo.CUSTOMER.CUSTOMER_SARARY = 0)";

                if (loanId != "")
                {
                    sqlQuery += "and [LOAN_ID] = " + loanId;
                }
                if (name != "")
                {
                    sqlQuery += "and [CUSTOMER_NAME] like '%" + name + "%'";
                }
                if (lastName != "")
                {
                    sqlQuery += "and [CUSTOMER_LASTNAME] like '%" + lastName + "%'";
                }

                DBBase.ConncetDatabase();

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();

                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                    DBBase.DisConncetDatabase();
                    return dt;
                }
                else
                {
                    DBBase.DisConncetDatabase();
                    return null;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllLoan(string loanId = "", string name = "", string lastName = "", int empId = 0,string mode = "")
        {
            try
            {
                string sqlQuery = @"SELECT dbo.LOAN.LOAN_ID, dbo.CUSTOMER.CUSTOMER_NAME, dbo.CUSTOMER.CUSTOMER_LASTNAME, dbo.LOAN.INTERREST_ID, 
                      dbo.LOAN.DOWN_PAYMENT_ID, dbo.LOAN.LOAN_DATE, dbo.LOAN.DOWN_PAYMENT, dbo.LOAN.PAY_MONTH, dbo.LOAN.DEBT_MONTH_REST, 
                      dbo.LOAN.LOAN_STATUS, dbo.DOWN_PAYMENT_INFO.DOWN_PAYMENT_RATE, dbo.INTERREST_INFO.INTERREST_RATE, 
                      dbo.INTERREST_INFO.DEBT_MONTH, dbo.LOAN.PRINCIPLE
                        FROM dbo.CUSTOMER INNER JOIN
                      dbo.LOAN ON dbo.CUSTOMER.CUSTOMER_ID = dbo.LOAN.CUSTOMER_ID INNER JOIN
                      dbo.DOWN_PAYMENT_INFO ON dbo.LOAN.DOWN_PAYMENT_ID = dbo.DOWN_PAYMENT_INFO.DOWN_PAYMENT_ID INNER JOIN
                      dbo.INTERREST_INFO ON dbo.LOAN.INTERREST_ID = dbo.INTERREST_INFO.INTERREST_ID INNER JOIN
                      dbo.SCHEDULE ON dbo.LOAN.LOAN_ID = dbo.SCHEDULE.LOAN_ID WHERE (dbo.LOAN.LOAN_STATUS <> 'I' AND dbo.LOAN.LOAN_STATUS <> 'F') 
                        AND (dbo.SCHEDULE.EMPLOYEE_ID = '" + empId + "') ";

                //if (mode.Equals("approve")) 
                //{
                //    sqlQuery += " AND (dbo.LOAN.LOAN_STATUS <> 'C') ";
                //}

                if (loanId != "")
                {
                    sqlQuery += " and [LOAN_ID] = " + loanId;
                }
                if (name != "")
                {
                    sqlQuery += " and [CUSTOMER_NAME] like '%" + name + "%'";
                }
                if (lastName != "")
                {
                    sqlQuery += " and [CUSTOMER_LASTNAME] like '%" + lastName + "%'";
                }

                DBBase.ConncetDatabase();

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();

                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                    DBBase.DisConncetDatabase();
                    return dt;
                }
                else
                {
                    DBBase.DisConncetDatabase();
                    return null;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static int LoadLoanID(int cusId)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [LOAN_ID] AS [LOAN_ID] FROM [CAR_LOAN].[dbo].[LOAN]
                                                    WHERE [CUSTOMER_ID] = @CUSTOMER_ID ORDER BY [LOAN_ID] DESC", DBBase.con);
                command.Parameters.AddWithValue("@CUSTOMER_ID", cusId);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0]["LOAN_ID"].ToString());
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        public static bool CheckLoan(int cusId)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [LOAN_ID] FROM [CAR_LOAN].[dbo].[LOAN]
                                                    WHERE [CUSTOMER_ID] = @CUSTOMER_ID and [LOAN_STATUS] in ('A','B','C','D')", DBBase.con);
                command.Parameters.AddWithValue("@CUSTOMER_ID", cusId);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public static void Insert(LoanItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[LOAN]
                                                       ([CAR_ID]
                                                       ,[INTERREST_ID]
                                                       ,[DOWN_PAYMENT_ID]
                                                       ,[CUSTOMER_ID]
                                                       ,[VEHICLE_ID]
                                                       ,[LOAN_DATE]
                                                       ,[DOWN_PAYMENT]
                                                       ,[PAY_MONTH]
                                                       ,[DEBT_MONTH_REST]
                                                       ,[LOAN_STATUS]
                                                       ,[DEBT_STATUS]
                                                       ,[PRINCIPLE])
                                                 VALUES
                                                      ( @CAR_ID
                                                       ,@INTERREST_ID
                                                       ,@DOWN_PAYMENT_ID
                                                       ,@CUSTOMER_ID
                                                       ,@VEHICLE_ID
                                                       ,@LOAN_DATE
                                                       ,@DOWN_PAYMENT
                                                       ,@PAY_MONTH
                                                       ,@DEBT_MONTH_REST
                                                       ,@LOAN_STATUS
                                                       ,@DEBT_STATUS
                                                       ,@PRINCIPLE)", DBBase.con);

                command.Parameters.AddWithValue("@CAR_ID", item.CAR_ID);
                command.Parameters.AddWithValue("@INTERREST_ID", item.INTERREST_ID);
                command.Parameters.AddWithValue("@DOWN_PAYMENT_ID", item.DOWN_PAYMENT_ID);
                command.Parameters.AddWithValue("@CUSTOMER_ID", item.CUSTOMER_ID);
                command.Parameters.AddWithValue("@VEHICLE_ID", item.VEHICLE_ID);
                command.Parameters.AddWithValue("@LOAN_DATE", item.LOAN_DATE);
                command.Parameters.AddWithValue("@DOWN_PAYMENT", item.DOWN_PAYMENT);
                command.Parameters.AddWithValue("@PAY_MONTH", item.PAY_MONTH);
                command.Parameters.AddWithValue("@DEBT_MONTH_REST", item.DEBT_MONTH_REST);
                command.Parameters.AddWithValue("@LOAN_STATUS", item.LOAN_STATUS);
                command.Parameters.AddWithValue("@DEBT_STATUS", item.DEBT_STATUS);
                command.Parameters.AddWithValue("@PRINCIPLE", item.PRINCIPLE);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool Update(LoanItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[LOAN]
                                               SET [VEHICLE_ID] = @VEHICLE_ID
                                                  ,[LOAN_STATUS] = @LOAN_STATUS
                                                  ,[CHECK_IDENTITY_CARD] = @CHECK_IDENTITY_CARD
                                                  ,[CHECK_REGISTRATION] = @CHECK_REGISTRATION
                                                  ,[CHECK_SALARY_SLIP] = @CHECK_SALARY_SLIP
                                                  ,[CHECK_ACCOUNT] = @CHECK_ACCOUNT
                                             WHERE ([LOAN_ID] = '" + item.LOAN_ID + "')", DBBase.con);

                command.Parameters.AddWithValue("@VEHICLE_ID", item.VEHICLE_ID);
                command.Parameters.AddWithValue("@LOAN_STATUS", item.LOAN_STATUS);
                command.Parameters.AddWithValue("@CHECK_IDENTITY_CARD", item.CHECK_IDENTITY_CARD);
                command.Parameters.AddWithValue("@CHECK_REGISTRATION", item.CHECK_REGISTRATION);
                command.Parameters.AddWithValue("@CHECK_SALARY_SLIP", item.CHECK_SALARY_SLIP);
                command.Parameters.AddWithValue("@CHECK_ACCOUNT", item.CHECK_ACCOUNT);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static void UpdateDeptStatus(LoanItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[LOAN]
                                               SET [DEBT_STATUS] = @DEBT_STATUS
                                             WHERE ([LOAN_ID] = @LOAN_ID)", DBBase.con);

                command.Parameters.AddWithValue("@LOAN_ID", item.LOAN_ID);
                command.Parameters.AddWithValue("@DEBT_STATUS", item.DEBT_STATUS);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool UpdateHead(LoanItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[LOAN]
                                               SET [VEHICLE_ID] = @VEHICLE_ID
                                                  ,[LOAN_STATUS] = @LOAN_STATUS
                                                  ,[LOAN_SUCCESS_DATE] = @LOAN_SUCCESS_DATE
                                             WHERE ([LOAN_ID] = '" + item.LOAN_ID + "')", DBBase.con);

                command.Parameters.AddWithValue("@VEHICLE_ID", item.VEHICLE_ID);
                command.Parameters.AddWithValue("@LOAN_STATUS", item.LOAN_STATUS);
                command.Parameters.AddWithValue("@LOAN_SUCCESS_DATE", item.LOAN_SUCCESS_DATE);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool UpdatePayMonth(LoanItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[LOAN]
                                               SET [DEBT_MONTH_REST] = @DEBT_MONTH_REST
                                             WHERE ([LOAN_ID] = @LOAN_ID)", DBBase.con);

                command.Parameters.AddWithValue("@LOAN_ID", item.LOAN_ID);
                command.Parameters.AddWithValue("@DEBT_MONTH_REST", item.DEBT_MONTH_REST);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool CheckFinishPayMonth(int cusId)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [DEBT_MONTH_REST] FROM [CAR_LOAN].[dbo].[LOAN]
                                                    WHERE [CUSTOMER_ID] = @CUSTOMER_ID AND DEBT_MONTH_REST > 0 AND LOAN_STATUS = 'I'", DBBase.con);
                command.Parameters.AddWithValue("@CUSTOMER_ID", cusId);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}